﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using StuManModels;
using StuManDAL.Common;

namespace StuManDAL
{
    public class StudentService
    {
        #region 增、删、改学员
        public bool IsIDNumberExist(string number,out string info)
        {
            string sql = $"select count(*) from Student where IDNumber='{number}'";
            try
            {
                bool result = Convert.ToInt32(SQLHelper.Instance.GetSingleResult(sql)) == 0;
                info = result ? "无重复" : "重复";
                return result;
            }
            catch (Exception ex)
            {
                info = "出现异常，信息为" + ex.Message;
                return false;
            }
        }
        /// <summary>
        /// 添加学员
        /// </summary>
        /// <param name="student"></param>
        /// <param name="info"></param>
        public void AddStudent(Student student,out string info)
        {
            string sql=$"insert into Student(StuName,Gender,Birthday,IDNumber,Age,PhoneNumber,StuAddress,CardNo,StuClassId) " +
                            $"values('{student.StuName}', {Convert.ToInt32(student.IsMale)}, '{student.Birthday:yyyy-MM-dd}', '{student.IDNumber}', {student.Age}, " +
                            $"'{student.PhoneNumber}', '{student.Address}', '{student.CardNo}', {student.StuClassId})";
            try
            {
                if (SQLHelper.Instance.Update(sql)==1)
                {
                    info = "添加学员成功";
                }
                else
                {
                    info = "添加学员失败";
                }
            }
            catch (Exception ex)
            {
                info = ex.Message;
            }
        }
        /// <summary>
        /// 删除学员
        /// </summary>
        /// <param name="stuId"></param>
        /// <param name="info"></param>
        public void DeleteStudent(int stuId,out string info)
        {
            string sql = $"delete from Student where StuId={stuId}";
            try
            {
                info = SQLHelper.Instance.Update(sql) == 1 ? "删除学员成功" : "删除学员失败";
            }
            catch(SqlException ex)
            {
                if (ex.Number==547)
                {
                    info = "该学员被其他表引用，不能删除";
                }
                else
                {
                    info = "数据库操作出现异常，异常信息为" + ex.Message;
                }
            }
            catch (Exception ex)
            {
                info = ex.Message;
            }
        }
        /// <summary>
        /// 修改学员信息
        /// </summary>
        /// <param name="student"></param>
        /// <param name="info"></param>
        public void ModifyStudent(StudentExt student,out string info)
        {
            string sql = $"update Student set StuName='{student.StuName}',Gender={Convert.ToInt32(student.IsMale)},Birthday='{student.Birthday:yyyy-MM-dd}',IDNumber='{student.IDNumber}'," +
                            $"Age={student.Age},PhoneNumber='{student.PhoneNumber}',StuAddress='{student.Address}',CardNo='{student.CardNo}',StuClassId={student.StuClassId} where StuId={student.StuId}";
            try
            {
                info = SQLHelper.Instance.Update(sql) == 1 ? "修改学员信息成功" : "修改学员信息失败";
            }
            catch (SqlException ex)
            {
                info = $"数据库操作出现异常,具体信息为:{ex.Message}";
            }
            catch(Exception ex)
            {
                info = ex.Message;
            }
        }
        #endregion
        #region 查询学员
        /// <summary>
        /// 根据学号查询学员
        /// </summary>
        /// <param name="stuId">学号</param>
        /// <param name="info">操作结果信息</param>
        /// <returns>学员信息</returns>
        public StudentExt QueryStudentByStuId(int stuId,out string info)
        {
            string sql = $"select StuId,StuName,Gender,Birthday,ClassName,IDNumber,Age,PhoneNumber,StuAddress,CardNo,ClassId from Student " +
                              $"inner join StudentClass on Student.StuClassId = StudentClass.ClassId where StuId = {stuId}";
            try
            {
                SqlDataReader reader = SQLHelper.Instance.GetReader(sql);
                StudentExt studentExt = null;
                if (reader.Read())
                {
                    studentExt = new StudentExt()
                    {
                        StuId = Convert.ToInt32(reader["StuId"]),
                        StuName = reader["StuName"].ToString(),
                        IsMale=Convert.ToBoolean(Convert.ToInt32(reader["Gender"])),
                        Gender = Convert.ToInt32(reader["Gender"]) == 1 ? "男" : "女",
                        Birthday = Convert.ToDateTime(reader["Birthday"]),
                        ClassName = reader["ClassName"].ToString(),
                        StuClassId=Convert.ToInt32(reader["ClassId"]),
                        IDNumber = reader["IDNumber"].ToString(),
                        Age = Convert.ToInt32(reader["Age"]),
                        PhoneNumber = reader["PhoneNumber"].ToString(),
                        Address = reader["StuAddress"].ToString(),
                        CardNo = reader["CardNo"].ToString()
                    };
                }
                reader.Close();
                info = "读取完成";
                return studentExt;
            }
            catch (SqlException ex)
            {
                info = "操作数据库出现异常，异常信息为：" + ex.Message;
                return null;
            }
            catch(Exception ex)
            {
                info = ex.Message;
                return null;
            }
        }
        /// <summary>
        /// 根据班级查询学员
        /// </summary>
        /// <param name="classId">班级编号</param>
        /// <param name="info">操作结果信息</param>
        /// <returns>学员信息</returns>
        public List<StudentExt> QueryStudentsByClass(int classId,out string info)
        {
            string sql = $"select StuId,StuName,Gender,Birthday,IDNumber,Age,CardNo,StuClassId from Student where StuClassId={classId}";
            try
            {
                SqlDataReader reader = SQLHelper.Instance.GetReader(sql);
                List<StudentExt> students = new List<StudentExt>();
                while (reader.Read())
                {
                    students.Add(new StudentExt()
                    {
                        StuId = Convert.ToInt32(reader["StuId"]),
                        StuName = reader["StuName"].ToString(),
                        IsMale = Convert.ToBoolean(Convert.ToInt32(reader["Gender"])),
                        Gender = Convert.ToInt32(reader["Gender"])==1?"男":"女",
                        Birthday = Convert.ToDateTime(reader["Birthday"]),
                        IDNumber = reader["IDNumber"].ToString(),
                        Age = Convert.ToInt32(reader["Age"]),
                        CardNo=reader["CardNo"].ToString(),
                        StuClassId=Convert.ToInt32(reader["StuClassId"])
                    });
                }
                reader.Close();
                info = "查询成功";
                return students;
            }
            catch (SqlException ex)
            {
                info = "操作数据库出现异常，异常信息为：" + ex.Message;
                return null;
            }
            catch (Exception ex)
            {
                info = ex.Message;
                return null;
            }
        }
        #endregion
    }
}
